# _*_ coding:utf-8 _*_
# @Time  : 2023.07.31
# @Author: zizlee
import pathlib
import time

from urllib3 import disable_warnings
import pandas as pd
import openpyxl
import requests

disable_warnings()


def full2half(u_string):
    # """ 全角转半角 """
    r_string = ""
    for uchar in u_string:
        inside_code = ord(uchar)
        if inside_code == 12288:  # 全角空格直接转换
            inside_code = 32
        elif 65281 <= inside_code <= 65374:  # 全角字符（除空格）根据关系转化
            inside_code -= 65248
        else:
            pass
        r_string += chr(inside_code)
    return r_string


class NewTemplate:
    def __init__(self, filename, sheet_name=None, ruizhi_start_id: int = 1):
        self.filename = filename
        self.sheet_name = sheet_name
        self.ruizhi_start_id = ruizhi_start_id

    def read_file_rows(self):
        workbook = openpyxl.load_workbook(self.filename, read_only=True)
        index_list = []
        for sheet_name in workbook.sheetnames:
            if self.sheet_name and sheet_name != self.sheet_name:
                continue
            variety_en = sheet_name.split('-')[1]
            df = pd.read_excel(self.filename, sheet_name=self.sheet_name, engine='openpyxl', skiprows=[0])
            df = df[['WORK组', 'RUIZHI组', '数据名称', '数据ID', '频率', '单位', '数据起始日期', '数据来源']]
            # df.dropna(inplace=True)
            if df.empty:
                continue
            df['数据起始日期'] = df['数据起始日期'].apply(pd.to_datetime)
            df['数据起始日期'] = df['数据起始日期'].apply(lambda x: x.strftime('%Y-%m-%d'))
            df['数据名称'] = df['数据名称'].apply(lambda x: full2half(x).strip())
            df['数据ID'] = df['数据ID'].apply(lambda x: x.strip())
            df['频率'] = df['频率'].apply(lambda x: x.strip())
            df['单位'] = df['单位'].apply(lambda x: '' if pd.isna(x) else x.strip())
            df['数据来源'] = df['数据来源'].apply(lambda x: x.strip())
            for row in df.to_dict(orient='records'):
                if row['数据ID'].startswith('R00') or len(row['数据ID']) > 10:
                    continue
                if len(row['数据ID']) == 8:
                    row['plat'] = 'wind'
                elif len(row['数据ID']) == 10:
                    row['plat'] = 'ths'
                else:
                    continue
                row['variety_en'] = variety_en
                index_list.append(row)
        print(f"读取文件数据完成,总{len(index_list)}行.")
        return index_list

    def index_exist_work_system(self, item) -> int:
        # GET: 存在r.json()['data'] = {id: xxx} 不存在r.json()['data'] = {}
        url = 'https://210.13.218.130:9000/v1/datalib/table/exists/'
        r = requests.get(url, params={'plat': item['source_plat'], 'sid': item['source_id']}, verify=False)
        exist = r.json()['data']
        return exist['id'] if exist else -1

    def index_exist_ruizhi_system(self, item) -> bool:
        url = 'http://210.13.218.130:9001/v1/admin/dataindex/exist/'
        r = requests.get(url, params={'plat': item['plat'], 'plat_id': item['plat_id']})
        exist = r.json()['data']['exist']
        return exist

    def create_index_to_work_system(self, item):
        # POST
        url = 'https://210.13.218.130:9000/v1/datalib/table/'
        r = requests.post(url, json=item, verify=False)
        print('向工作系统添加指标结果:', r.json())

    def create_index_to_ruizhi_system(self, item):
        url = "http://210.13.218.130:9001/v1/admin/dataindex/create/"
        r = requests.post(url, json=item)
        t = 'THS指标' if item['plat'] == 'ths' else 'WIND指标'
        self.ruizhi_start_id += 1
        str_i = '%06d' % self.ruizhi_start_id
        update_str = f'{str_i},1,{t},,{item["frequency"]},{item["plat_id"]},,,,{item["variety_en"]},{item["in_work_id"]},,{item["name"]}'
        with open('更新配置行.txt', 'a+', encoding='utf8') as f:
            f.write(update_str + '\n')
        print('向瑞智系统添加指标结果:', r.json())

    def get_work_index_params(self, item):
        return {
            'source_plat': item['plat'],
            'source_id': item['数据ID'],
            'name_zh': item['数据名称'],
            'frequency': item['频率'],
            'unit': item['单位'],
            'raw_source': item['数据来源'],
            'startdate': item['数据起始日期'],
            'variety': item['variety_en'],
            'three_plat': '',
            'note': '',
            'state': 1,
            'is_open': 1
        }

    def get_ruizhi_index_params(self, item):
        return {
            'name': item['name_zh'],
            'frequency': item['frequency'],
            'unit': item['unit'],
            'start_date': item['startdate'],
            'plat': item['source_plat'],
            'plat_id': item['source_id'],
            'source': item['raw_source'],
            'note': item['note'],
            'variety_en': item['variety']
        }

    def add_index_list(self, index_list):
        t_length = len(index_list)
        for i, row in enumerate(index_list, 1):
            print(f'{i}/{t_length}', f'{round(i/t_length * 100, 2)}%')
            index_work = self.get_work_index_params(row)
            index_ruizhi = self.get_ruizhi_index_params(index_work)
            in_work_id = self.index_exist_work_system(index_work)
            if in_work_id == -1:
                # 向工作系统添加指标
                self.create_index_to_work_system(index_work)
                in_work_id = self.index_exist_work_system(index_work)  # 再次获取ID

            index_ruizhi['in_work_id'] = in_work_id if in_work_id > -1 else ''
            in_ruizhi = self.index_exist_ruizhi_system(index_ruizhi)
            if not in_ruizhi:
                # 向瑞智系统添加指标
                self.create_index_to_ruizhi_system(index_ruizhi)
            time.sleep(1)

    def query_work_index_object(self, source_id: str):
        url = 'https://210.13.218.130:9000/v1/datalib/table/'
        r = requests.get(url, params={'kw': source_id}, verify=False)
        index_obj = r.json()['data']['records']
        if len(index_obj) > 1:
            raise ValueError('查询ID有误，不止一条记录')
        if len(index_obj) < 1:
            raise ValueError('查询的数据指标不存在!{}'.format(source_id))
        return index_obj[0]

    def add_to_work_group(self, index_list, user_id: int, test: bool = False):
        df = pd.DataFrame(index_list)
        df['WORK组'].fillna(method='ffill', inplace=True)
        index_list = df.to_dict(orient='records')
        url = 'https://210.13.218.130:9000/v1/administrator/datalib/groupIndex/'
        t_length = len(index_list)
        for i, row in enumerate(index_list, 1):
            if i <= 17:
                continue
            if test:
                print(row)
            else:
                obj = self.query_work_index_object(source_id=row['数据ID'])
                group_index = {
                    'index_id': obj['id'],
                    'name': obj['name_zh'],
                    'formula': f'{obj["source_plat"]}_{obj["source_id"]}',
                    'frequency': obj['frequency'],
                    'unit': obj['unit'],
                    'is_diff': False,
                    'vcode': row['variety_en'],
                    'user_id': user_id,
                    'group_id': row['WORK组'],
                    'source_plat': obj['source_plat'],
                    'source_id': obj['source_id']
                }
                r = requests.post(url, json=group_index, verify=False)
                print(f'{i}/{t_length}', f'{round(i / t_length * 100, 2)}%', r.json())
                time.sleep(1)

    def add_to_ruizhi_group(self, index_list):
        pass


if __name__ == '__main__':
    handler = NewTemplate(filename='数据库建设V2.0/宏观经济(海外宏观).xlsx', sheet_name='海外宏观-SC', ruizhi_start_id=9016)
    rows = handler.read_file_rows()
    # handler.add_index_list(index_list=rows)  # 向2个系统添加指标
    handler.add_to_work_group(index_list=rows, user_id=160, test=False)


